package recommenRatings.data;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import recommenRatings.domain.Message;
import recommenRatings.domain.Photo;
import recommenRatings.domain.User;
import recommenRatings.utils.UIDGenerator;

public class JDBCUserDAO implements IUserDAO {

	public User select(String userPOID) {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null, stmt2 = null;
		User u = new User();
		ResultSet result = null, result2 = null;
		try {

			String sql = "SELECT * FROM User WHERE (UserPOID = ?)";
			stmt = conn.prepareStatement(sql); // conn es una conexión válida
			stmt.setString(1, userPOID);
			result = stmt.executeQuery();
			result.next();
			u.setName(result.getString("name"));
			u.setSurname(result.getString("surname"));
			u.setAddress(result.getString("address"));
			u.setPhoneNumber(result.getString("telephone"));
			u.setMobilePhone(result.getString("mobilephone"));
			u.setBirthDate(result.getDate("birthdate"));
			u.setEmail(result.getString("email"));
			String s = result.getString("photoPOID");
			String sql2 = "SELECT * FROM Photo WHERE (photoPOID = ?) ";
			stmt2 = conn.prepareStatement(sql2); // conn es una conexión
			// válida
			stmt2.setString(1, s);
			result2 = stmt2.executeQuery();
			result2.next();
//			try {
//				u.setPhoto(new Photo(result2.getDate("uploadDate"), result2
//						.getBlob("photo")));
//			} catch (SQLException e) {
//				System.out.println("Message: " + e.getMessage());
//				System.out.println("SQLState: " + e.getSQLState());
//				System.out.println("ErrorCode: " + e.getErrorCode());
//			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}

		return u;
	}

	public List<User> select() {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null, stmt2 = null;
		List<User> searchResults = new LinkedList<User>();
		ResultSet result = null, result2 = null;

		try {

			String sql = "SELECT * FROM User ";
			stmt = conn.prepareStatement(sql);// conn es una conexión válida
			stmt.executeQuery();
			result = stmt.executeQuery();

			while (result.next()) {
				User temp = new User();
				temp.setName(result.getString("name"));
				temp.setSurname(result.getString("surname"));
				temp.setAddress(result.getString("address"));
				temp.setPhoneNumber(result.getString("telephone"));
				temp.setMobilePhone(result.getString("mobilephone"));
				temp.setBirthDate(result.getDate("birthdate"));
				temp.setEmail(result.getString("email"));
				String s = result.getString("photoPOID");
				String sql2 = "SELECT * FROM Photo WHERE (photoPOID = ?) ";
				stmt2 = conn.prepareStatement(sql2); // conn es una conexión
				// válida
				stmt2.setString(1, s);
				result2 = stmt2.executeQuery();
				result2.next();
				try {
					temp.setPhoto(new Photo(result2.getString("photo")
							,result2.getDate("uploadDate")));
				} catch (SQLException e) {
					System.out.println("Message: " + e.getMessage());
					System.out.println("SQLState: " + e.getSQLState());
					System.out.println("ErrorCode: " + e.getErrorCode());
				}
				searchResults.add(temp);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}

		return searchResults;
	}

	public List<User> selectSearch(String statement) {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null, stmt2 = null;
		List<User> searchResults = new LinkedList<User>();
		ResultSet result = null, result2 = null;
		if (statement != null && statement != "") {
			try {

				String sql = "SELECT * FROM User " + statement;
				stmt = conn.prepareStatement(sql); // conn es una conexión
				// válida
				stmt.executeQuery();
				result = stmt.executeQuery();

				while (result.next()) {
					User temp = new User();
					temp.setName(result.getString("name"));
					temp.setSurname(result.getString("surname"));
					temp.setAddress(result.getString("address"));
					temp.setPhoneNumber(result.getString("telephone"));
					temp.setMobilePhone(result.getString("mobilephone"));
					temp.setBirthDate(result.getDate("birthdate"));
					temp.setEmail(result.getString("email"));
					if (result.getString("photoPOID") != null) {
						String s = result.getString("photoPOID");
						String sql2 = "SELECT * FROM Photo WHERE (photoPOID = ?) ";
						stmt2 = conn.prepareStatement(sql2); // conn es una
						// conexión
						// válida
						stmt2.setString(1, s);
						result2 = stmt2.executeQuery();
						result2.next();
						try {
							temp.setPhoto(new Photo(result2
									.getString("photo"),result2
									.getDate("uploadDate")));
						} catch (SQLException e) {
							System.out.println("Message: " + e.getMessage());
							System.out.println("SQLState: " + e.getSQLState());
							System.out
									.println("ErrorCode: " + e.getErrorCode());
						}
					}
					searchResults.add(temp);
				}
			} catch (SQLException e) {
				System.out.println("Message: " + e.getMessage());
				System.out.println("SQLState: " + e.getSQLState());
				System.out.println("ErrorCode: " + e.getErrorCode());
			} finally {
				ConnectionManager.getInstance().checkIn(conn);
				try {
					if (result != null)
						result.close();
					if (stmt != null)
						stmt.close();
				} catch (SQLException e) {
				}

			}
		} else {// TODO Hacer algo cuando los datos no son correctos.

		}
		return searchResults;
	}

	public String selectUserOID(Connection conn, String username) {

		PreparedStatement stmt = null;
		String uoid = null;
		ResultSet result = null;

		try {

			String sql = "SELECT UserPOID FROM User WHERE (username=?)";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, username);// conn es una conexión válida
			result = stmt.executeQuery();
			result.next();
			uoid = result.getString("UserPOID");
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}

		return uoid;

	}
	public String selectUserOID(String username) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String uoid = null;
		ResultSet result = null;

		try {

			String sql = "SELECT UserPOID FROM User WHERE (username=?)";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, username);// conn es una conexión válida
			result = stmt.executeQuery();
			result.next();
			uoid = result.getString("UserPOID");
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}

		return uoid;

	}
	
	
	public boolean insert(User u) {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;

		boolean result = false;
		try {

			String sql = "INSERT INTO User(name,surname,gender,birthdate,address,city,state,telephone,mobilephone,email,username,password,UserPOID) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
			stmt = conn.prepareStatement(sql); // conn es una conexión
			// válida
			stmt.setString(1, u.getName());
			stmt.setString(2, u.getSurname());
			stmt.setBoolean(3, u.getGender());
			stmt.setDate(4, new Date(u.getBirthDate().getTime()));
			stmt.setString(5, u.getAddress());
			stmt.setString(6, u.getCity());
			stmt.setString(7, u.getState());
			stmt.setString(8, u.getPhoneNumber());
			stmt.setString(9, u.getMobilePhone());
			stmt.setString(10, u.getEmail());
			stmt.setString(11, u.getUsername());
			stmt.setString(12, u.getPassword());
			stmt.setString(13, UIDGenerator.getInstance().getKey());
			stmt.execute();
			if(stmt.getUpdateCount()>0){
				result=true;
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}

		return result;

	}
	public User selectUser(String userPOID) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null, stmt2 = null;
		User u = new User();
		ResultSet result = null, result2 = null;
		try {

			String sql = "SELECT * FROM User WHERE (UserPOID = ?)";
			stmt = conn.prepareStatement(sql); // conn es una conexión válida
			stmt.setString(1, userPOID);
			result = stmt.executeQuery();
			result.next();
			u.setName(result.getString("name"));
			u.setSurname(result.getString("surname"));
			u.setAddress(result.getString("address"));
			u.setPhoneNumber(result.getString("telephone"));
			u.setMobilePhone(result.getString("mobilephone"));
			u.setBirthDate(result.getDate("birthdate"));
			u.setEmail(result.getString("email"));
			String s = result.getString("photoPOID");
			String sql2 = "SELECT * FROM Photo WHERE (photoPOID = ?) ";
			stmt2 = conn.prepareStatement(sql2); // conn es una conexión
			// válida
			stmt2.setString(1, s);
			result2 = stmt2.executeQuery();
			result2.next();
//			try {
//				u.setPhoto(new Photo(result2.getDate("uploadDate"), result2
//						.getBlob("photo")));
//			} catch (SQLException e) {
//				System.out.println("Message: " + e.getMessage());
//				System.out.println("SQLState: " + e.getSQLState());
//				System.out.println("ErrorCode: " + e.getErrorCode());
//			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}

		return u;
	}
	public Map<String,String> selectAllFriends(String username) {
		Connection conn = ConnectionManager.getInstance().checkOut();

		PreparedStatement stmt = null;
		Map<String,String> searchResults = new HashMap<String, String>();
		ResultSet result = null;

		try {

			String sql = "SELECT User2 FROM Friends WHERE (User1 = ?)";
			stmt = conn.prepareStatement(sql); // conn es una conexion valida
			stmt.setString(1, new JDBCUserDAO().selectUserOID(conn, username));
			result = stmt.executeQuery();

			while (result.next()) {
				String currentPOID = result.getString("User2"); 
				User auxUser = select(currentPOID);
				String name = auxUser.getName()+" "+auxUser.getSurname();
				searchResults.put(currentPOID, name);
			}
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			ConnectionManager.getInstance().checkIn(conn);
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}

		}
		return searchResults;
	}

}
